Search Results

Microsoft Release SQL Server 2016 SP1 and Give Stuff Away!

Today was the Connection (); 2016 Keynote, and it was certainly was worth tuning into to catch-up with what Microsoft have been up to. I was going to write a brief summary of the Keynote and made some notes in a text file, but an hour in to the 2 hour presentation I gave up because there was so much going on. Microsoft have been pushing for an Incremental Service Modelling , to the point that they changed the recommendation from Cumulative Updates being deployed only if you are affected by any of the issues to always update . This

SSDTPokedex: Migrating a Database Into SSDT

Hello!   If you want to have the best chance of something being successful, you have to be committed to it from the start. That’s a pretty fatuous sounding statement, almost as bad as “to make something better you have to do more of the good stuff and less of the bad stuff”, so let me contextualise: if you desire to have good testing coverage on an application, then you need to be serious about testing form the first day you write code for the application. Be it manual or automated testing, you need to put the effort in terms

How To Use MSBuild Arguments in Visual Studio Builds

When creating MSBuild.proj files for builds in Visual Studio, there are times when you might want some targets called, and other times you don't want the targets called. In my case, our Production builds compile all the web and windows service solutions as well asthe deployment scripts for databases and our BIDS projects. Essentially everything I would want if I was going to run an entire deployment for a release. However when deploying a dll hotfix, I don't want the SQL scripts and BIDS stuff built. When we want to deploy a hotfix, speed is of the essence. Our build

SqlServer PowerShell Modules NuGet Package Now Available

Hello! Back in the July Update of SSMS 2016 , a bunch of new SQL PowerShell functions were added, plus two neat additions to Invoke-Sqlcmd : -outputas, which allowed you to output the result set into a data object (eg, data row, data table etc), and -ConnectionString, which allows you to pass in a connection string instead of using the pre-defined parameters. All very useful stuff, go and have a read . However, this update has two issues: firstly, it's not updating the classic sqlps module, but rather has created a new module: sqlserver. This new module will be regularly

SqlServer PowerShell Modules NuGet Package Now Available

Hello! Back in the July Update of SSMS 2016 , a bunch of new SQL PowerShell functions were added, plus two neat additions to Invoke-Sqlcmd : -outputas, which allowed you to output the result set into a data object (eg, data row, data table etc), and -ConnectionString, which allows you to pass in a connection string instead of using the pre-defined parameters. All very useful stuff, go and have a read . However, this update has two issues: firstly, it's not updating the classic sqlps module, but rather has created a new module: sqlserver. This new module will be regularly

PowerShell Workflow Script To Stop VM’s In A Resource Group

Recently I needed to make sure that all the VM’s in a given resource group were stopped, and so I looked around the Runbooks available to download from the Azure Marketplace. Some of these were ridiculously complex: one was over 500 lines long! Just to stop a VM! Naturally there is a need to setup: we need to get the names of the VM’s in the Resource Group and, if they are running, then stop them. However the command to stop a VM is straightforward: “Stop-AzureRmVM”, followed by the name of  the VM and the resource group. Quite frankly I’m

Are you running VMware ESX 6 Update 1 and connecting to SQL - be warned

If you are running any of your application on windows 2012 and up and on VMware ESX 6 with Update 1 connecting to SQL you must read on. We’ve been working at a client recently on an issue where requests to SQL have a 500ms latency between requests. What’s really odd is that the time seems to be lost between the client code making a request and the request coming to SQL. Profiler shows a gap of 500ms between the end time of one request and the start time of another request. The issue would only affect some applications and

Uploading Files To Data Lake Storage With PowerShell Part Two

Carrying on from our previous post on automating the process of uploading files to  Azure Data Lake Store , we will check if a Resource Group exists, and if it does not then it will create it. Although the entire script is available on Git (posted below) I’m going to go into one function per post so that I can go in greater depth. Part One of this blog series focused on logging in to an Azure Subscription. Today’s function starts on line 42 and is called Set-AzureResourceGroup. Before we go into it though, I want to take a moment

SSDT 16.5 Released part 2: Using the DacFx API and Samples!

Hello! Yesterday I posted about the new release of SSDT from the SQL Tools Team at Microsoft. Two of the big changes are the ability to create the deployment report, deployment script and execute the deployment all in one command. The other change is that now for Azure two scripts are generated: one for any changes that need a connection to master, and the other script for changes to the user database. The samples yesterday showed how to execute the new method using SQLPackage, but a lot of people, myself included, have automated the deployment using the DacFx API through

Migrating SSIS Packages to SSIS Azure part Two – Automating the Deployment

Hello! If you’ve read and followed through my previous post, you will have World Wide Importers Integration Services project running in SSIS Azure. It’s all very interesting, go and have a read . One thing that is missing form that guide, the documentation, and SSIS in general, is how to automate SSIS Deployments. In the WWI SSIS project, there are connection managers that we had to manually update the values of to get it to work post-deploy. This is exactly the opposite of what we want to do. Back when SQL Server 2012 was known as Denali, one of the

Getting started with Azure Policy

Recently as part of a data classification implementation certain aspects were implemented using Azure Policy, since is supports auditing of existing resources and can prevent non-compliant resources from being created in the first instance. Like most things, the documentation reads well and the samples seem useful, until one has to do something different and go off-piste. There are plenty of VM and tagging samples though not so many for Azure SQL Database. Therefore as Sabin is a data engineering consultancy, all examples given here will be Azure SQL Database focused and not the canonical VM as found in many examples.

Git: How to work on a feature branch but pull in later commits from another branch?

On a client site last week the question was raised: I want to work on a feature for a project which will take longer than other people merging their branches into the dev branch. I therefore want to merge any changes on the dev branch into mine, test my changes before pushing back to the dev branch, and then and ultimately master for a release. We are using a git flow methodology whereby the development manager will merge feature branches from developers’ branches via pull requests into the dev branch. Tests are run and if they pass, we merge into

Assist Deploy Is Available on GitHub

Hello! For some time now I have been working on automating SSIS deployments, and earlier this week I published my efforts on GitHub . But before I get into the what/how, let’s focus on the why and let me catch you up on how I got here… The task to take an ispac and deploy in and of itself is quite a straightforward process as there are multiple ways to do this . For those of you who want the abridged version of the linked post, the choices are as follows: Integration Services Deploy Wizard SSIS Catalog T-SQL API PowerShell

SSIS Package Execution In Azure Is Now Available

Well, it’s been some time coming but SSIS packages are the latest product to make the move from on premise to Azure. You can now take your SSIS projects and deploy them to the new Platform as a Service (PaaS) offering in Azure. The aim of the team at Microsoft was for users to take their current SSIS packages and just “lift and shift” these to Azure. So in development terms that means that there are minimum to no changes to be made in the solution at least. But before we get into the deployment and running of SSIS packages